﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.Odbc;

namespace HitachiService.DBAccess
{
    public class ProjectAccess : DataAccess
    {
        public static ProjectData GetProjectById(int id)
        {
            String sql =
                "select " +
                "gid,project_level,parent_id,action_date,action_time,route_name,dev_id,custom_name,dev_floor,contract_type,custom_type,floor_type " +
                "from t_project " +
                "where gid=" + id;
            ProjectData result = null;
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);

            conn.Open();
            OdbcDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                ProjectData project = new ProjectData();
                project.ID = ReadInt(reader["gid"]);
                project.Level = ReadInt(reader["project_level"]);
                project.ParentID = ReadInt(reader["parent_id"]);
                project.ActionDate = ReadInt(reader["action_date"]);
                project.ActionTime = ReadInt(reader["action_time"]);
                project.Name = ReadString(reader["route_name"]);
                project.DevID = ReadString(reader["dev_id"]);
                project.CustomName = ReadString(reader["custom_name"]);
                project.DevFloor = ReadString(reader["dev_floor"]);
                project.ContractType = ReadString(reader["contract_type"]);
                project.CustomType = ReadString(reader["custom_type"]);
                project.FloorType = ReadString(reader["floor_type"]);
                result = project;
                break;
            }
            conn.Close();
            return result;
        }

        public static List<ProjectData> GetOffspringProjects(ProjectData prj, int level)
        {
            List<ProjectData> result = new List<ProjectData>();
            result.Add(prj);
            if (prj.ID == prj.ParentID)
                return result;
            List<ProjectData> children = GetProjectsByParentId(prj.ID);
            foreach (ProjectData p in children)
            {
                result.AddRange(GetOffspringProjects(p, level+1));
            }
            return result;
        }

        public static List<ProjectData> GetProjectsByParentId(int parent_id)
        {
            String parent_id_str = parent_id == 0 ? " is NULL" : "=" + parent_id;
            String sql =
                "select " +
                "gid,project_level,parent_id,action_date,action_time,route_name,dev_id,custom_name,dev_floor,contract_type,custom_type,floor_type " +
                "from t_project " +
                "where parent_id" + parent_id_str;
            List<ProjectData> result = new List<ProjectData>();
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);
            conn.Open();
            OdbcDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                ProjectData project = new ProjectData();
                project.ID = ReadInt(reader["gid"]);
                project.Level = ReadInt(reader["project_level"]);
                project.ParentID = ReadInt(reader["parent_id"]);
                project.ActionDate = ReadInt(reader["action_date"]);
                project.ActionTime = ReadInt(reader["action_time"]);
                project.Name = ReadString(reader["route_name"]);
                project.DevID = ReadString(reader["dev_id"]);
                project.CustomName = ReadString(reader["custom_name"]);
                project.DevFloor = ReadString(reader["dev_floor"]);
                project.ContractType = ReadString(reader["contract_type"]);
                project.CustomType = ReadString(reader["custom_type"]);
                project.FloorType = ReadString(reader["floor_type"]);
                result.Add(project);
            }
            conn.Close();
            return result;
        }

        private static List<ProjectData> GetChildMasterProjects(ProjectData parent_data)
        {
            List<ProjectData> result = new List<ProjectData>();
            if (parent_data.Level > 0)
            {
                List<ProjectData> l1 = GetProjectsByParentId(parent_data.ID);
                List<ProjectData> l2 = new List<ProjectData>();
                foreach(ProjectData p in l1)
                {
                    List<ProjectData> tmp = GetChildMasterProjects(p);
                    l2.AddRange(tmp);
                }
                result.AddRange(l1);
                result.AddRange(l2);
            }
            return result;
        }

        public static List<ProjectData> GetProjectsByResourceId(int resource_id)
        {
            String sql =
                "select " +
                "p.gid,p.project_level,p.parent_id,p.action_date,p.action_time,p.route_name,p.dev_id,p.custom_name,p.dev_floor,p.contract_type,p.custom_type,p.floor_type " +
                "from t_project p, t_prj_res pr " +
                "where p.gid=pr.project_id and pr.resource_id=" + resource_id;
            List<ProjectData> result = new List<ProjectData>();
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);
            conn.Open();
            OdbcDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                ProjectData project = new ProjectData();
                project.ID = ReadInt(reader["gid"]);
                project.Level = ReadInt(reader["project_level"]);
                project.ParentID = ReadInt(reader["parent_id"]);
                project.ActionDate = ReadInt(reader["action_date"]);
                project.ActionTime = ReadInt(reader["action_time"]);
                project.Name = ReadString(reader["route_name"]);
                project.DevID = ReadString(reader["dev_id"]);
                project.CustomName = ReadString(reader["custom_name"]);
                project.DevFloor = ReadString(reader["dev_floor"]);
                project.ContractType = ReadString(reader["contract_type"]);
                project.CustomType = ReadString(reader["custom_type"]);
                project.FloorType = ReadString(reader["floor_type"]);
                result.Add(project);
            }
            conn.Close();
            // Get Child Masters
            Dictionary<int, bool> loaded_map = new Dictionary<int, bool>();
            List<ProjectData> added_child = new List<ProjectData>();
            foreach (ProjectData p in result)
            {
                List<ProjectData> child = GetChildMasterProjects(p);
                added_child.AddRange(child);
            }

            Dictionary<int, ProjectData> all = new Dictionary<int, ProjectData>();
            foreach (ProjectData p in result)
            {
                all.Add(p.ID, p);
            }
            List<ProjectData> added = new List<ProjectData>();
            while (true)
            {
                added.Clear();
                foreach (ProjectData p in all.Values)
                {
                    if (p.ParentID>0 && !all.ContainsKey(p.ParentID))
                    {
                        ProjectData parent = GetProjectById(p.ParentID);
                        if (parent != null)
                        {
                            added.Add(parent);
                        }
                    }
                }
                if (added.Count == 0)
                    break;
                foreach (ProjectData p in added)
                {
                    all.Add(p.ID, p);
                    result.Add(p);
                }
            }
            result.AddRange(added_child);
            return result;
        }

        public static List<ProjectData> GetProjectsByLevel(int level)
        {
            String level_str = level == 0 ? " is NULL" : "=" + level;
            String sql =
                "select " +
                "gid,project_level,parent_id,action_date,action_time,route_name,dev_id,custom_name,dev_floor,contract_type " +
                "from t_project " +
                "where parent_id" + level_str;
            List<ProjectData> result = new List<ProjectData>();
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);
            conn.Open();
            OdbcDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                ProjectData project = new ProjectData();
                project.ID = (int)reader["gid"];
                project.Level = ReadInt(reader["project_level"]);
                project.ParentID = (int)reader["parent_id"];
                project.ActionDate = (int)reader["action_date"];
                project.ActionTime = (int)reader["action_time"];
                project.Name = (String)reader["route_name"];
                project.DevID = (String)reader["dev_id"];
                project.CustomName = (String)reader["custom_name"];
                project.DevFloor = (String)reader["dev_floor"];
                project.ContractType = (String)reader["contract_type"];
                result.Add(project);
            }
            conn.Close();
            return result;
        }

        public static bool UpdateProject(ProjectData project_data)
        {
            String sql =
                "update t_project set " +
                " project_level=" + project_data.Level +
                ",parent_id=" + project_data.ParentID +
                ",action_date=" + project_data.ActionDate +
                ",action_time=" + project_data.ActionTime +
                ",route_name='" + project_data.Name + "'" +
                ",dev_id='" + project_data.DevID + "'" +
                ",custom_name='" + project_data.CustomName + "'" +
                ",dev_floor='" + project_data.DevFloor + "'" +
                ",contract_type='" + project_data.ContractType + "'" +
                " where gid=" + project_data.ID;
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);

            conn.Open();
            int n = cmd.ExecuteNonQuery();
            conn.Close();
            return n>0;
        }

        public static bool UpdateProjects(List<ProjectData> project_data_list)
        {
            foreach (ProjectData pd in project_data_list)
            {
                if (!UpdateProject(pd))
                    return false;
            }
            return true;
        }

        public static bool AddProject(ProjectData project_data)
        {
            String sql =
                "insert into t_project " +
                "(project_level,parent_id,action_date,action_time,route_name,dev_id,custom_name,dev_floor,contract_type,custom_type,floor_type)" +
                " values " +
                "("+ project_data.Level+","+
                project_data.ParentID + "," +
                project_data.ActionDate + "," +
                project_data.ActionTime + ",'" +
                project_data.Name + "','" +
                project_data.DevID + "','" +
                project_data.CustomName + "','" +
                project_data.DevFloor + "','" +
                project_data.ContractType + "','" +
                project_data.CustomType + "','" +
                project_data.FloorType + "'" +
                ")" +
                "";
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);

            conn.Open();
            int n = cmd.ExecuteNonQuery();
            conn.Close();
            return n > 0;
        }

        public static bool DeleteProject(ProjectData project_data)
        {
            String sql =
                "delete from t_project " +
                " where gid=" + project_data.ID;
            OdbcConnection conn = GetConnection();
            OdbcCommand cmd = new OdbcCommand(sql, conn);
            conn.Open();
            int n = cmd.ExecuteNonQuery();
            conn.Close();
            return n > 0;
        }

    }
}